using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Text;
using DTO;

namespace DAO
{
    public class HocSinhDao
    {
        public DataTable LayBang()
        {
            DataTable dt = new DataTable();
            OleDbConnection cn;
            // B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            cn = DataProvider.ConnectionData();
            // B3: Tao chuoi strSQL thao tac CSDL
            string strSQL;
            strSQL = "Select * From HocSinh";
            OleDbDataAdapter da;
            da = new OleDbDataAdapter(strSQL, cn);
            da.Fill(dt);
            // B5: Dong ket noi CSDL
            cn.Close();
            return dt;
        }

        public void CapNhatBang(DataTable dt)
        {
            OleDbConnection cn;
            // B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            cn = DataProvider.ConnectionData();
            // B3: Tao chuoi strSQL thao tac CSDL
            string strSQL;
            strSQL = "Select * From HocSinh";
            OleDbDataAdapter da;
            da = new OleDbDataAdapter(strSQL, cn);
            OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
            da.Update(dt);
            // B5: Dong ket noi CSDL
            cn.Close();
        }

        public IList LayDanhSach()
        {
            ArrayList ds = new ArrayList();
            // B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn;
            cn = DataProvider.ConnectionData();
            // B3: Tao chuoi strSQL thao tac CSDL
            string strSQL;
            strSQL = "Select * From HocSinh";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            OleDbDataReader dr;
            dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                HocSinhDto hsDto = new HocSinhDto();
                hsDto.Ma = (int)dr["Ma"];
                hsDto.Ten = (string)dr["Ten"];
                hsDto.NgaySinh = (DateTime)dr["NgaySinh"];
                hsDto.DiaChi = (string)dr["DiaChi"];
                hsDto.Toan = (double)dr["Toan"];
                hsDto.Ly = (double)dr["Ly"];
                hsDto.Hoa = (double)dr["Hoa"];
                hsDto.DTB = (double)dr["DTB"];
                hsDto.MaLop = (int)dr["MaLop"];
                ds.Add(hsDto);
            }
            // B5: Dong ket noi CSDL
            dr.Close();
            cn.Close();
            return ds;
        }

        public HocSinhDto TimKiem(int maHocSinh)
        {
            HocSinhDto hsDto = null;
            OleDbConnection cn;
            // B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            cn = DataProvider.ConnectionData();
            // B3: Tao chuoi strSQL thao tac CSDL
            string strSQL;
            strSQL = "Select * From HocSinh Where Ma = ?";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@Ma", OleDbType.Integer);
            cmd.Parameters["@Ma"].Value = maHocSinh;
            OleDbDataReader dr;
            dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                hsDto = new HocSinhDto();
                hsDto.Ma = (int)dr["Ma"];
                hsDto.Ten = (string)dr["Ten"];
                hsDto.NgaySinh = (DateTime) dr["NgaySinh"];
                hsDto.DiaChi = (string)dr["DiaChi"];
                hsDto.Toan = (double)dr["Toan"];
                hsDto.Ly = (double)dr["Ly"];
                hsDto.Hoa = (double)dr["Hoa"];
                hsDto.DTB = (double)dr["DTB"];
                hsDto.MaLop = (int)dr["MaLop"];
            }
            // B5: Dong ket noi CSDL
            dr.Close();
            cn.Close();
            return hsDto;
        }

        public void Them(HocSinhDto hsDto)
        {
            OleDbConnection cn;
            // B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            cn = DataProvider.ConnectionData();
            // B3: Tao chuoi strSQL thao tac CSDL
            string strSQL;
            strSQL = "Insert into HocSinh(Ten, NgaySinh, DiaChi, Toan, Ly, Hoa, DTB, MaLop) values (?, ?, ?, ?, ?, ?, ?, ?)" +
            "";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@Ten", OleDbType.WChar);
            cmd.Parameters.Add("@NgaySinh", OleDbType.Date);
            cmd.Parameters.Add("@DiaChi", OleDbType.WChar);
            cmd.Parameters.Add("@Toan", OleDbType.Double);
            cmd.Parameters.Add("@Ly", OleDbType.Double);
            cmd.Parameters.Add("@Hoa", OleDbType.Double);
            cmd.Parameters.Add("@DTB", OleDbType.Double);
            cmd.Parameters.Add("@MaLop", OleDbType.Integer);
            cmd.Parameters["@Ten"].Value = hsDto.Ten;
            cmd.Parameters["@NgaySinh"].Value = hsDto.NgaySinh;
            cmd.Parameters["@DiaChi"].Value = hsDto.DiaChi;
            cmd.Parameters["@Toan"].Value = hsDto.Toan;
            cmd.Parameters["@Ly"].Value = hsDto.Ly;
            cmd.Parameters["@Hoa"].Value = hsDto.Hoa;
            cmd.Parameters["@DTB"].Value = hsDto.DTB;
            cmd.Parameters["@MaLop"].Value = hsDto.MaLop;
            cmd.ExecuteNonQuery();
            strSQL = "Select @@IDENTITY";
            cmd = new OleDbCommand(strSQL, cn);
            hsDto.Ma = (int)cmd.ExecuteScalar();
            // B5: Dong ket noi CSDL
            cn.Close();
        }

        public void Xoa(int maHocSinh)
        {
            OleDbConnection cn;
            // B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            cn = DataProvider.ConnectionData();
            // B3: Tao chuoi strSQL thao tac CSDL
            string strSQL;
            strSQL = "Delete From HocSinh Where Ma = ? ";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@Ma", OleDbType.Integer);
            cmd.Parameters["@Ma"].Value = maHocSinh;
            cmd.ExecuteNonQuery();
            // B5: Dong ket noi CSDL
            cn.Close();
        }

        public void Sua(HocSinhDto hsDto)
        {
            OleDbConnection cn;
            // B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            cn = DataProvider.ConnectionData();
            // B3: Tao chuoi strSQL thao tac CSDL
            string strSQL;
            strSQL = "Update HocSinh Set Ten = ? and NgaySinh = ? and DiaChi = ? and Toan = ? and Ly = ? and Hoa = ? and DTB = ? and MaLop = ? Where Ma = ?";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@Ten", OleDbType.WChar);
            cmd.Parameters.Add("@NgaySinh", OleDbType.Date);
            cmd.Parameters.Add("@DiaChi", OleDbType.WChar);
            cmd.Parameters.Add("@Toan", OleDbType.Double);
            cmd.Parameters.Add("@Ly", OleDbType.Double);
            cmd.Parameters.Add("@Hoa", OleDbType.Double);
            cmd.Parameters.Add("@DTB", OleDbType.Double);
            cmd.Parameters.Add("@MaLop", OleDbType.Integer);
            cmd.Parameters.Add("@Ma", OleDbType.Integer);
            cmd.Parameters["@Ten"].Value = hsDto.Ten;
            cmd.Parameters["@NgaySinh"].Value = hsDto.NgaySinh;
            cmd.Parameters["@DiaChi"].Value = hsDto.DiaChi;
            cmd.Parameters["@Toan"].Value = hsDto.Toan;
            cmd.Parameters["@Ly"].Value = hsDto.Ly;
            cmd.Parameters["@Hoa"].Value = hsDto.Hoa;
            cmd.Parameters["@DTB"].Value = hsDto.DTB;
            cmd.Parameters["@MaLop"].Value = hsDto.MaLop;
            cmd.Parameters["@Ma"].Value = hsDto.Ma;
            cmd.ExecuteNonQuery();
            // B5: Dong ket noi CSDL
            cn.Close();
        }

        public DataTable TimKiem(HocSinhCrt hsCrt)
        {
            DataTable dt = new DataTable();
            OleDbConnection cn;
            cn = DataProvider.ConnectionData();
            OleDbCommand cmd = BuildQuery(hsCrt, cn);
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            da.Fill(dt);
            return dt;
        }

        private OleDbCommand BuildQuery(HocSinhCrt hsCrt, OleDbConnection cn)
        {
            OleDbCommand cmd = new OleDbCommand();
            string strDKMaHocSinh = " 1=1 ";
            string strDKTenHocSinh = " 1=1 ";
            string strDKNgaySinh = " 1=1 ";
            string strDKDiaChi = " 1=1 ";
            string strDKDiemToan = " 1=1 ";
            string strDKDiemLy = " 1=1 ";
            string strDKDiemHoa = " 1=1 ";
            string strDKDTB = " 1=1 ";
            string strDKLopHoc = " 1=1 ";
            if ((hsCrt.Ma != 0))
            {
                strDKMaHocSinh = " Ma = ? ";
                cmd.Parameters.Add("@Ma", OleDbType.Integer);
                cmd.Parameters["@Ma"].Value = hsCrt.Ma;
            }
            if ((hsCrt.Ten != ""))
            {
                strDKTenHocSinh = " Ten like ?";
                cmd.Parameters.Add("@Ten", OleDbType.WChar);
                cmd.Parameters["@Ten"].Value = ("%"+ (hsCrt.Ten + "%"));
            }
            if (hsCrt.CheckNgaySinh)
            {
                strDKNgaySinh = " NgaySinh between ? and ? ";
                cmd.Parameters.Add("@NgaySinhTu", OleDbType.Date);
                cmd.Parameters.Add("@NgaySinhDen", OleDbType.Date);
                cmd.Parameters["@NgaySinhTu"].Value = hsCrt.NgaySinhTu;
                cmd.Parameters["@NgaySinhDen"].Value = hsCrt.NgaySinhDen;
            }
            if ((hsCrt.CheckDiaChi
                        && (hsCrt.DiaChi != "")))
            {
                strDKDiaChi = " DiaChi like \'?\' ";
                cmd.Parameters.Add("@DiaChi", OleDbType.WChar);
                cmd.Parameters["@DiaChi"].Value = ("%"
                            + (hsCrt.DiaChi + "%"));
            }
            if (hsCrt.CheckToan)
            {
                strDKDiemToan = " Toan between ? and ? ";
                cmd.Parameters.Add("@DiemToanTu", OleDbType.Double);
                cmd.Parameters.Add("@DiemToanDen", OleDbType.Double);
                cmd.Parameters["@DiemToanTu"].Value = hsCrt.ToanTu;
                cmd.Parameters["@DiemToanDen"].Value = hsCrt.ToanDen;
            }
            if (hsCrt.CheckLy)
            {
                strDKDiemLy = " Ly between ? and ? ";
                cmd.Parameters.Add("@DiemLyTu", OleDbType.Double);
                cmd.Parameters.Add("@DiemLyDen", OleDbType.Double);
                cmd.Parameters["@DiemLyTu"].Value = hsCrt.ToanTu;
                cmd.Parameters["@DiemLyDen"].Value = hsCrt.ToanDen;
            }
            if (hsCrt.CheckHoa)
            {
                strDKDiemHoa = " Hoa between ? and ? ";
                cmd.Parameters.Add("@DiemHoaTu", OleDbType.Double);
                cmd.Parameters.Add("@DiemHoaDen", OleDbType.Double);
                cmd.Parameters["@DiemHoaTu"].Value = hsCrt.ToanTu;
                cmd.Parameters["@DiemHoaDen"].Value = hsCrt.ToanDen;
            }
            if (hsCrt.CheckDTB)
            {
                strDKDTB = " DTB between ? and ? ";
                cmd.Parameters.Add("@DTBTu", OleDbType.Double);
                cmd.Parameters.Add("@DTBDen", OleDbType.Double);
                cmd.Parameters["@DTBTu"].Value = hsCrt.ToanTu;
                cmd.Parameters["@DTBDen"].Value = hsCrt.ToanDen;
            }
            if (hsCrt.CheckLopHoc)
            {
                strDKLopHoc = " MaLop = ? ";
                cmd.Parameters.Add("@MaLop", OleDbType.Integer);
                cmd.Parameters["@MaLop"].Value = hsCrt.MaLop;
            }
            string strDKWhere = " Where ";
            strDKWhere = (strDKWhere + strDKMaHocSinh);
            strDKWhere = (strDKWhere + (" and " + strDKTenHocSinh));
            strDKWhere = (strDKWhere + (" and " + strDKNgaySinh));
            strDKWhere = (strDKWhere + (" and " + strDKDiaChi));
            strDKWhere = (strDKWhere + (" and " + strDKDiemToan));
            strDKWhere = (strDKWhere + (" and " + strDKDiemLy));
            strDKWhere = (strDKWhere + (" and " + strDKDiemHoa));
            strDKWhere = (strDKWhere + (" and " + strDKDiemToan));
            strDKWhere = (strDKWhere + (" and " + strDKDTB));
            strDKWhere = (strDKWhere + (" and " + strDKLopHoc));
            string strSQL = "Select * From HocSinh ";
            strSQL = (strSQL + strDKWhere);
            strSQL += " Order by Ten ";
            cmd.Connection = cn;
            cmd.CommandText = strSQL;
            return cmd;
        }
    }
}
